﻿using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Gollie.WebCache;
using MySql.Data.MySqlClient;
using NLog;

namespace MySQL.Db
{
    enum ResultType
    {
        Scalar,
        SingleRow,
        MultiRow,
        NonQuery,
		MultiResultSet,
        Identity
    }


    public class Query
    {
		private static Logger logger = LogManager.GetCurrentClassLogger();
        private string name;
        private QueryBuilder queryBuilder;
        private QueryArguments arguments;
       // private static MySqlCommand identityCommand = new MySqlCommand("SELECT LAST_INSERT_ID();");

        internal CommandType CommandType { get { return this.queryBuilder.CommandType; } }
		internal string CommandText { get { return this.queryBuilder.QueryString.ToString(); } }
		internal QueryArguments Arguments { get { return this.arguments; } }
		internal ResultType ResultType { get { return this.queryBuilder.ResultType; } }

        public Query() // to be used for common queries, like GetUserId()
        {
        }

        public Query(string name)
            : this(name, (QueryArguments)null)
        {
        }

		public Query(string name, string argName, string argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, bool argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, short argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, ushort argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, int argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, uint argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, long argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, ulong argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, DateTime argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, Decimal argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

		public Query(string name, string argName, byte[] argValue)
			: this(name, new QueryArguments()) { this.arguments.Add(argName, argValue); }

        public Query(string name, QueryArguments arguments)
        {
            this.name = name;

            this.arguments = arguments;

            this.queryBuilder = DataCache.GetCacheableObject<QueryBuilder>("query_" + name, name);
        }

        public object Execute(string argToReplace, string replaceBy)
        {
            StringBuilder commandString = this.queryBuilder.QueryString;

            commandString.Replace(argToReplace, replaceBy);

            return this.DoExecute(commandString);
        }

        /// <summary>
        /// Returns:
        /// - ResultSet if type = multirow or singlerow
        /// - object if type = scalar
        /// - int if type = nonquery or identity
        /// </summary>
        /// <returns></returns>
        public object Execute()
        {
            return this.DoExecute(this.queryBuilder.QueryString);
        }

        private object DoExecute(StringBuilder commandString)
        {
			object retVal;

			using (var connection = new MySqlConnection(DbUtils.GetConnectionString(this.Db)))
			{
				using (MySqlCommand command = new MySqlCommand(commandString.ToString()) { CommandType = this.CommandType, Connection = connection } )
				{
					command.SetParameters(this.arguments);

					if (this.Db == GollieDb.Gollie)
					{
						logger.Debug("Executing query {0}", command.CommandText);
						if (command.Parameters.Count > 0)
						{
							logger.Debug("\t\tArguments:");
							foreach (MySqlParameter par in command.Parameters)
							{
								logger.Debug("\t\t{0} = {1}", par.ParameterName, par.Value);
							}
						}
					}

					connection.Open();

					switch (this.queryBuilder.ResultType)
					{
						case ResultType.NonQuery: retVal = this.ExecuteNonQuery(command);
							break;
						case ResultType.Scalar: retVal = this.ExecuteScalar(command);
							break;
						//case ResultType.Identity: retVal = this.ExecuteIdentity(command);
						//	break;
						case ResultType.MultiResultSet: retVal = this.ExecuteMultiReader(command);
							break;
						default: retVal = this.ExecuteReader(command);
							break;
					}

					if (retVal is System.DBNull)
						retVal = null;
				}
			}

            return retVal;
        }

		private ResultSetCollection ExecuteMultiReader(MySqlCommand command)
		{
			ResultSetCollection resultSets;

			using (MySqlDataReader reader = command.ExecuteReader())
			{
				resultSets = new ResultSetCollection(reader);
			}

			if (this.Db == GollieDb.Gollie)
				logger.Debug("Resultset contains {0} resultsets", resultSets.Count);

			return resultSets;
		}

        private ResultSet ExecuteReader(MySqlCommand command)
        {
			ResultSet resultSet;

			using (MySqlDataReader reader = command.ExecuteReader())
			{
				resultSet = new ResultSet(reader);
			}
			
			if (this.Db == GollieDb.Gollie)
				logger.Debug("Resultset contains {0} rows", resultSet.Count);

            return resultSet;
        }

        private object ExecuteScalar(MySqlCommand command)
        {

            object retVal = command.ExecuteScalar();

			if (this.Db == GollieDb.Gollie)
				logger.Debug("Returned {0}", retVal ?? "{NULL}");

            return retVal;
        }

        private int ExecuteNonQuery(MySqlCommand command)
        {
            int rows = command.ExecuteNonQuery();

			if (this.Db == GollieDb.Gollie)
				logger.Debug("Affected rows: {0}", rows);

            return rows;
        }

		//private int ExecuteIdentity(MySqlCommand command)
		//{
		//	int identity = -1;

		//	Query.identityCommand.Connection = command.Connection;

		//	int rowsAffected = (int)(command.ExecuteNonQuery());

		//	if (rowsAffected > 0)
		//		identity = (int)Query.identityCommand.ExecuteScalar();

		//	return identity;
		//}

        public GollieDb Db
        {
            get
            {
                if (this.queryBuilder == null)
                    return GollieDb.Gollie;

                return this.queryBuilder.Db;
            }
        }
    }
}
